/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Category;
import com.tces.model.Choice;
import com.tces.model.Question;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author arnolda
 */
public class QuestionDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(QuestionDAO.class);

    /**
     * This will retrieve all the questions that is visible base on the
     *
     * @param categoryID
     * @param categoryID
     * @return active question list
     * @throws Exception
     */
    public static List<Question> getActiveQuestions(int categoryID) throws Exception {
        List<Question> questionList = new ArrayList<Question>();
        String sql = "SELECT\n"
                + "c.category_id,\n"
                + "c.categery_name,\n"
                + "c.category_percentage,\n"
                + "cq.categories_questions_order,\n"
                + "q.question_id,\n"
                + "q.question_descrption,\n"
                + "q.question_visible\n"
                + "FROM\n"
                + "categories AS c\n"
                + "INNER JOIN categories_questions AS cq ON cq.category_id = c.category_id\n"
                + "INNER JOIN questions AS q ON q.question_id = cq.question_id\n"
                + "WHERE c.category_visible=1\n"
                + "AND q.question_visible=1\n"
                + "AND c.category_id=?";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, categoryID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Question question;
        while (rs.next()) {
            question = new Question();
            question.setCategoryID(rs.getInt("category_id"));
            question.setCategoryPercentage(rs.getDouble("category_percentage"));
            question.setCategoryName(rs.getString("categery_name"));
            question.setQuestionDescription(rs.getString("question_descrption"));
            question.setQuestionID(rs.getInt("question_id"));
            question.setQuestionVisible(rs.getInt("question_visible"));
            question.setCategoriesQuestionsOrder(rs.getInt("categories_questions_order"));
            questionList.add(question);
        }
        closeConnection(getConnectionObject());
        return questionList;
    }

    public static List<Choice> getActiveQuestionsResponse(int scheduleID, int questionID) throws Exception {
        List<Choice> questionList = new ArrayList<Choice>();
        String sql = "SELECT\n"
                + "	tbl.choice_id,\n"
                + "	choice_value,\n"
                + "	choice_description,\n"
                + "	choice_visible,\n"
                + "	choice_numeric_value,\n"
                + "	IFNULL(response_count, 0) response_count\n"
                + "FROM\n"
                + "	(\n"
                + "		SELECT\n"
                + "			c.choice_id,\n"
                + "			c.choice_value,\n"
                + "			c.choice_description,\n"
                + "			c.choice_visible,\n"
                + "			c.choice_numeric_value\n"
                + "		FROM\n"
                + "			choices AS c\n"
                + "		INNER JOIN questions_choices AS qc ON c.choice_id = qc.choice_id\n"
                + "		WHERE\n"
                + "			qc.question_id = ?\n"
                + "		ORDER BY qc.questions_choices_order\n"
                + "	) tbl \n"
                + "LEFT JOIN (\n"
                + "	SELECT\n"
                + "		se.choice_id,\n"
                + "		Count(1) AS response_count\n"
                + "	FROM\n"
                + "		subject_evaluations AS se\n"
                + "	WHERE\n"
                + "		se.subject_schedule_id = ?\n"
                + "	AND se.question_id = ?\n"
                + "	GROUP BY\n"
                + "		se.choice_id\n"
                + ") tbl2 ON tbl.choice_id = tbl2.choice_id";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, questionID);
        preparedStatement.setInt(2, scheduleID);
        preparedStatement.setInt(3, questionID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Choice choice;
        while (rs.next()) {
            choice = new Choice();
            choice.setChoiceID(rs.getInt("choice_id"));
            choice.setChoiceValue(rs.getString("choice_value"));
            choice.setChoiceDescription(rs.getString("choice_description"));
//            choice.setQuestionDescription(rs.getString("question_descrption"));
            choice.setChoiceNumericValue(rs.getInt("choice_numeric_value"));
            choice.setChoiceVisible(rs.getInt("choice_visible"));
//            choice.setQuestionsChoicesOrder(rs.getInt("questions_choices_order"));
            choice.setResponseCount(rs.getInt("response_count"));
            questionList.add(choice);
        }
        closeConnection(getConnectionObject());
        return questionList;
    }
}